<?php

namespace app\home\lib;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
//表头样式
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;

// https://github.com/php-fig/simple-cache/releases
// https://github.com/PHPOffice/PhpSpreadsheet/tree/1.18.0
class MyPhpExcel
{
	protected $letters = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ"];

	// protected $link = 'https://uploads.jiankangpao.sichuancancer.org/uploads';

	// protected $cacheTime = 5; // 缓存时间（秒）
	// protected $fontSize = [
	// 	'小五' => 9,
	// 	'五号' => 10.5,
	// 	'小四' => 12,
	// 	'四号' => 14,
	// 	'小二' => 18,
	// 	'二号' => 20,
	// ];

	// protected $alignment = ['vertical' => Alignment::VERTICAL_CENTER, 'rotation' => 0, 'wrap' => true];

	// protected $conf = [
	// 	'head' => [
	// 		'font' => ['bold' => true],
	// 		'alignment' => [
	// 			'horizontal' => Alignment::HORIZONTAL_CENTER,
	// 			'vertical' => Alignment::VERTICAL_CENTER
	// 		],
	// 		//'borders'=>[
	// 		//  'outline'=>['borderStyle' => Border::BORDER_MEDIUM, 'color' => ['rgb' => '508630']],
	// 		//  'bottom'=>['borderStyle'=>Border::BORDER_MEDIUM],
	// 		//],
	// 	],
	// 	'font' => [ //标题样式
	// 		'font' => [
	// 			'name' => '微软雅黑',
	// 			'size' => 12,
	// 			'bold' => true,
	// 			'italic' => false,
	// 			//'underline'=>Font::UNDERLINE_DOUBLE,
	// 			//'strike'=>false,
	// 			'color' => ['rgb' => 'ff0000']
	// 		],
	// 	],
	// 	'alignment_left' => ['vertical' => Alignment::VERTICAL_CENTER, 'rotation' => 0, 'wrap' => true, 'horizontal' => Alignment::HORIZONTAL_LEFT],
	// 	'alignment_right' => ['vertical' => Alignment::VERTICAL_CENTER, 'rotation' => 0, 'wrap' => true, 'horizontal' => Alignment::HORIZONTAL_RIGHT],
	// 	'alignment_center' => ['vertical' => Alignment::VERTICAL_CENTER, 'rotation' => 0, 'wrap' => true, 'horizontal' => Alignment::HORIZONTAL_CENTER],
	// ];

	/**
	 * 设置某个单元格的下拉列表规则
	 * @param Worksheet $sheet
	 * @param [string] $cellPoint 单元格坐标. A1
	 * @param [sting] $format 公式
	 * @return void
	 */
	protected function setValidation(Worksheet $sheet, $cellPoint, $format)
	{
		$validation = $sheet->getCell($cellPoint)->getDataValidation();
		$validation->setType(DataValidation::TYPE_LIST);
		$validation->setErrorStyle(DataValidation::STYLE_INFORMATION);
		$validation->setAllowBlank(false);
		// $validation->setShowInputMessage(true);
		// $validation->setShowErrorMessage(true);
		$validation->setShowDropDown(true);
		// $validation->setErrorTitle('输出错误');
		// $validation->setError('值不在列表中');
		$validation->setPromptTitle('请选择');
		// $validation->setPrompt('请从列表中选择一个值');
		$validation->setFormula1($format);
		// $validation->setFormula1('=major');//使用某个公式
		// $validation->setFormula1('"A1,A2,A3"');//可以直接写列表，用逗号分隔,最多255字符
		// $objValidation->setFormula1('=INDIRECT(F5)');//indirect间接的，以某个坐标为依托，联级
	}

	// 导出问答记录
	// http://192.168.8.130/azfzjkp/home/Qa/downloadRecordList.html?create_date=2024-04-15
	// http://192.168.8.130/azfzjkp/home/Qa/downloadRecordList.html?create_date=2024-04-16
	// http://192.168.8.130/azfzjkp/home/Qa/downloadRecordList.html?create_date=2024-04-17
	// http://192.168.8.130/azfzjkp/home/Qa/downloadRecordList.html?create_date=2024-04-18
	// http://192.168.8.130/azfzjkp/home/Qa/downloadRecordList.html?create_date=2024-04-19
	// http://192.168.8.130/azfzjkp/home/Qa/downloadRecordList.html?create_date=2024-04-20
	// http://192.168.8.130/azfzjkp/home/Qa/downloadRecordList.html?create_date=2024-04-21
	public function getRecordListExcel($excelFile)
	{
		set_time_limit(60*60);
		ini_set('memory_limit', '1024M');
		$md5_excel_url = md5($excelFile);
		
		cache($md5_excel_url, json_encode(['code' => 0, 'msg' => "开始导出数据···"]), 60 * 10); //10*60秒过期
		$create_date_from = session('recordList-create_date_from');
		$create_date_to = session('recordList-create_date_to');
		$search = session('recordList-search');
		$page_one_time = session('recordList-page_one_time');

		
		$where = [];
		if (!empty($create_date_from) && empty($create_date_to)) {
			$where['r.create_time'] = ['between', [$create_date_from . ' 00:00:00', $page_one_time]];
		} else if (!empty($create_date_to) && empty($create_date_from)) {
			$where['r.create_time'] = ['<=', $create_date_to . ' 23:59:59' > $page_one_time ? $page_one_time : $create_date_to . ' 23:59:59'];
		} else if (!empty($create_date_to) && !empty($create_date_from)) {
			$where['r.create_time'] = ['between', [$create_date_from . ' 00:00:00', $create_date_to . ' 23:59:59' > $page_one_time ? $page_one_time : $create_date_to . ' 23:59:59']];
		} else {
			$where['r.create_time'] = ['<', $page_one_time];
		}

		if (!empty($search)) {
			$where['u.nick_name'] = ['like', "%{$search}%"];
		}

		$userList = db("as_record")
			->field("r.openid,u.nick_name,max(r.create_time) end_time")
			->alias("r")
			->join("user_info u", "u.openid = r.openid")
			->where($where)
			->group('r.openid,u.nick_name')
			->order('end_time')
			->select();

		$recordList = [];
		foreach ($userList as $key => $user) {
			$openid = $user['openid'];
			$record = [];
			// 1取得昵称
			$userInfo = db("user_info")->field("nick_name")->where("openid", $openid)->find();
			if (!empty($userInfo)) {
				$userInfo['nick_name'] = iconv('UTF-8', 'GBK//IGNORE', $userInfo['nick_name']);
				$userInfo['nick_name'] = iconv('GBK', 'UTF-8//IGNORE', $userInfo['nick_name']);
			} else {
				$userInfo['nick_name'] = "微信用户";
			}
			$record['基本情况']['提交时间'] = $user['end_time'];
			$record['基本情况']['微信昵称'] = $userInfo['nick_name'];

			// 2取得答案
			$asList = db("as_record")
				->where('openid', $openid)
				->select();
			foreach ($asList as $key => $as) {
				// var_dump($as);
				if ($as['classify'] == '闯关题') {
					$record[$as['classify']]["q_no_{$as['q_no']}"] = ($as['is_right'] == 'yes' ? "[✓]" : "[×]") . "\r\n" . $as['my_answer'];
				} else {
					$record[$as['classify']]["q_no_{$as['q_no']}"] = $as['my_answer'];
				}
			}
			$recordList[] = $record;
		}
		// var_dump($recordList);

		$spreadsheet = new Spreadsheet();
		$spreadsheet->setActiveSheetIndex(0);
		$sheet = $spreadsheet->getActiveSheet();
		$spreadsheet->getDefaultStyle()->getFont()->setName('微软雅黑')->setSize(12);
		//设置选定sheet表名
		$sheet->setTitle('用户答案列表');
		$sheet->getStyle("A1:BZ1")->getAlignment()->setHorizontal('center'); //居中

		$line = 1; //开始行
		$row = 0; //开始列
		$rowNameList = [];
		$download_item_index = 1;

		// 基本信息
		$rowNameList['基本情况'] = [];
		$from = $row + 1;
		$sheet->setCellValue("{$this->letters[$row]}1", "基本情况");
		$rowNameList['基本情况']['提交时间'] = $row;
		$sheet->setCellValue("{$this->letters[$row++]}2", '提交时间');
		$rowNameList['基本情况']['微信昵称'] = $row;
		$sheet->getStyle("{$this->letters[$row]}2")->getFont()->setColor(new Color('FFFF0000'));
		$sheet->setCellValue("{$this->letters[$row++]}2", '微信昵称');

		$qaBankList = db("qa_bank")
			->field("id,q_no,region,title")
			->where('classify', '基本情况')
			->where("status", 1)
			->order('q_no asc')
			->select();
		foreach ($qaBankList as $key => $qaBank) {
			$rowNameList['基本情况']["q_no_{$qaBank['q_no']}"] = $row;
			// $sheet->setCellValue("{$this->letters[$row++]}2", $qaBank['title']);
			$sheet->setCellValue("{$this->letters[$row++]}2", $qaBank['q_no'] . "：" . $qaBank['title']);
		}
		$sheet->mergeCells([$from, 1, $row, 1]);
		$sheet->getStyle([$from, 1, $row, 2])->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($download_item_index++ % 2 == 1 ? 'FFC6E0B4' : 'FFB4C6E7');

		// 闯关题
		$rowNameList['闯关题'] = [];
		$from = $row + 1;
		$sheet->setCellValue("{$this->letters[$row]}1", "闯关题");
		$qaBankList = db("qa_bank")
			->field("id,q_no,region,title")
			->where('classify', '闯关题')
			->where("status", 1)
			->order('q_no asc')
			->select();
		foreach ($qaBankList as $key => $qaBank) {
			$rowNameList['闯关题']["q_no_{$qaBank['q_no']}"] = $row;
			// $sheet->setCellValue("{$this->letters[$row++]}2", $qaBank['title']);
			$sheet->setCellValue("{$this->letters[$row++]}2", $qaBank['q_no'] . "：" . $qaBank['title']);
		}
		$sheet->mergeCells([$from, 1, $row, 1]);
		$sheet->getStyle([$from, 1, $row, 2])->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($download_item_index++ % 2 == 1 ? 'FFC6E0B4' : 'FFB4C6E7');

		// 科普题
		$rowNameList['科普题'] = [];
		$from = $row + 1;
		$sheet->setCellValue("{$this->letters[$row]}1", "科普题");
		$qaBankList = db("qa_bank")
			->field("id,q_no,region,title")
			->where('classify', '科普题')
			->where("status", 1)
			->order('q_no asc')
			->select();
		foreach ($qaBankList as $key => $qaBank) {
			$rowNameList['科普题']["q_no_{$qaBank['q_no']}"] = $row;
			// $sheet->setCellValue("{$this->letters[$row++]}2", $qaBank['title']);
			$sheet->setCellValue("{$this->letters[$row++]}2", $qaBank['q_no'] . "：" . $qaBank['title']);
		}
		$sheet->mergeCells([$from, 1, $row, 1]);
		$sheet->getStyle([$from, 1, $row, 2])->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($download_item_index++ % 2 == 1 ? 'FFC6E0B4' : 'FFB4C6E7');

		// var_dump($rowNameList);
		// die();
		$line = 3; //开始行
		foreach ($recordList as $key => $record) {
			// 缓存处理状态
			cache($md5_excel_url, json_encode(['code' => 0, 'msg' => "已生成：{$key}条数据···"]), 60 * 10); //10*60秒过期

			foreach ($record as $classify => $as) {
				foreach ($as as $cowName => $value) {
					$sheet->setCellValue([$rowNameList[$classify][$cowName] + 1, $line], $value);
				}
			}
			// $sheet->getStyle("A{$line}:H{$line}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFBFBFBF'); //设置背景色
			$line++;
		}

		// 缓存处理状态
		cache($md5_excel_url, json_encode(['code' => 0, 'msg' => "导出完成：{$key}条数据，保存文件中···"]), 60 * 100); //10*60秒过期

		foreach ($this->letters as $key => $letter) {
			$sheet->getColumnDimension($letter)->setAutoSize(true); //自动调整宽度
		}

		// 添加边框样式到单元格
		$line--;
		$sheet->getStyle("A1:{$this->letters[$row - 1]}{$line}")
			->getBorders()->getAllBorders()
			->setBorderStyle(Border::BORDER_THIN)->setColor(new Color('FFFCE4D6'));

		$sheet->getStyle("A1:{$this->letters[$row - 1]}{$line}")
			->getAlignment()
			->setWrapText(true); // 设置自动换行

		$sheet->getStyle("A1:{$this->letters[$row - 1]}{$line}")
			->getAlignment()
			->setVertical(Alignment::VERTICAL_TOP); // 垂直对齐方式为居中

		// 选中第一格
		$sheet->getStyle("A2");

		$writer = new Xls($spreadsheet);

		// $excelFile = '肿瘤防治健康跑回答记录_' . $create_date . '.xls';
		$filePath = ROOT_PATH . 'public' . DS . 'uploads' . DS  . $excelFile;
		if (file_exists($filePath)) {
			unlink($filePath);
		}
		$writer->save($filePath);

		// /home/wwwroot/default/azfzjkp_dev/
		//释放内存，为了防止内存泄露，进行手动清理
		$spreadsheet->disconnectWorksheets();
		unset($spreadsheet);
		unset($list);

		// 缓存处理状态
		cache($md5_excel_url, json_encode(['code' => 1, 'msg' => "EXCEL文件生成结束。"]), 60 * 10); //10*60秒过期

		// http: //192.168.8.130/azfzjkp_dev/public/uploads/record_list_2024-04-05_22:15:07.xls
		// $url = "http://192.168.8.130/" . NOW_ENV . "/public/uploads" . DS  . $excelFile;
		$url = "https://jiankangpao.sichuancancer.org/" . NOW_ENV . "/public/uploads" . DS  . $excelFile;
		return $url;
	}

	// 生成EXCEL
	// https://jiankangpao.sichuancancer.org/azfzjkp_dev/home/QaBank/exportExcel/qa_main_id/96.html
	public function getExcel($qa_main_id)
	{
		// echo phpinfo();
		// die();
		$spreadsheet = new Spreadsheet();
		$spreadsheet->setActiveSheetIndex(0);
		$sheet = $spreadsheet->getActiveSheet();

		$qaMain = db("qa_main")->where("id", $qa_main_id)->find();
		if (empty($qaMain)) {
			// 应该不存在为空的情况
			$sheet->setCellValue('A1', '参数异常！');
		} else {
			$spreadsheet->getDefaultStyle()->getFont()->setName('微软雅黑')->setSize(12);
			//设置选定sheet表名
			$sheet->setTitle('题目列表');

			$sheet->getColumnDimension('A')->setWidth(5);
			$sheet->getColumnDimension('B')->setWidth(15);
			$sheet->getColumnDimension('C')->setWidth(15);

			$line = 2; //开始行
			$sheet->setCellValue("B{$line}", "问答名称：");
			$sheet->setCellValue("C{$line}", "{$qaMain['title']}");
			$sheet->getStyle("A{$line}:C{$line}")->getFont()->setSize(18)->setBold(true);

			$line++;
			$line++;
			$line++;
			// 示例说明
			$sheet->setCellValue("A{$line}", '示例：格式正确才能成功导入');
			$sheet->getStyle("A{$line}:C{$line}")->getFont()->setSize(12)->setColor(new Color('FFFF0000'));
			$line++;
			$sheet->setCellValue("A{$line}", '题号');
			$sheet->setCellValue("B{$line}", '因子名称');
			$sheet->setCellValue("C{$line}", '题型');
			$sheet->setCellValue("D{$line}", '问题')->getStyle("D{$line}")->getBorders()->getLeft()->setBorderStyle(Border::BORDER_MEDIUMDASHED)->setColor(new Color('FFFF0000'));
			$sheet->getStyle("A{$line}:Z{$line}")->getFont()->setSize(14)->setBold(true);

			$line++;
			$sheet->setCellValue("D{$line}", '图片(不是必须)')->getStyle("D{$line}")->getBorders()->getLeft()->setBorderStyle(Border::BORDER_MEDIUMDASHED)->setColor(new Color('FFFF0000'));
			$line++;
			$sheet->setCellValue("C{$line}", '分值1')
				->setCellValue("D{$line}", '选项1')->getStyle("D{$line}")->getBorders()->getLeft()->setBorderStyle(Border::BORDER_MEDIUMDASHED)->setColor(new Color('FFFF0000'));
			$line++;
			$sheet->setCellValue("C{$line}", '分值2')
				->setCellValue("D{$line}", '选项2')->getStyle("D{$line}")->getBorders()->getLeft()->setBorderStyle(Border::BORDER_MEDIUMDASHED)->setColor(new Color('FFFF0000'));
			$line++;
			$sheet->getStyle("A" . ($line - 6) . ":E{$line}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFBFBFBF'); //设置背景色

			$line = 13; //正文开始
			$sheet->setCellValue("A{$line}", '题目列表');
			$sheet->getStyle("A{$line}:C{$line}")->getFont()->setSize(16)->setBold(true);
			$line++;

			$sheet->getStyle("A{$line}:H{$line}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFBFBFBF'); //设置背景色
			$line++;

			$qaBankList = db("qa_bank")->where("qa_main_id", $qa_main_id)->order('id asc')->select();
			foreach ($qaBankList as $key => $qaBank) {
				$sheet->setCellValue("A{$line}", $key + 1);
				$sheet->setCellValue("B{$line}", $qaBank['classify']);
				$this->setValidation($sheet, "B{$line}", '"' . str_replace('，', ',', $qaMain['classify_set']) . '"'); // 下拉

				$sheet->setCellValue("C{$line}", $qaBank['type_tag']);
				$this->setValidation($sheet, "C{$line}", '"单选,多选-合计记分,多选-数量记分"'); // 下拉

				$sheet->setCellValue("D{$line}", $qaBank['title'])->getStyle("D{$line}")->getBorders()->getLeft()->setBorderStyle(Border::BORDER_MEDIUMDASHED)->setColor(new Color('FFFF0000'));

				$sheet->getStyle("A{$line}:Z{$line}")->getFont()->setSize(14)->setBold(true);

				if ($qaBank['type_tag'] == '多选') {

					$sheet->setCellValue("C{$line}", '多选-合计记分');
					if (strlen($qaBank['count_mode']) > 5) {
						$countMode = json_decode($qaBank['count_mode'], true);
						if (
							isset($countMode['mode']) &&
							$countMode['mode'] == 'above_count' &&
							$countMode['above'] > 0 &&
							$countMode['score'] > 0
						) {
							$sheet->setCellValue("C{$line}", '多选-数量记分');

							$line++;
							// 添加数量计分参数
							$sheet->setCellValue("B{$line}", "选择个数大于")->getStyle("B{$line}")->getAlignment()->setHorizontal('right');
							$sheet->setCellValue("C{$line}", $countMode['above'])->getStyle("C{$line}")->getFont()->setColor(new Color('FFFF0000'));
							$sheet->setCellValue("D{$line}", "得")->getStyle("D{$line}")->getAlignment()->setHorizontal('right');
							$sheet->setCellValue("E{$line}", $countMode['score'])->getStyle("E{$line}")->getFont()->setColor(new Color('FFFF0000'));
							$sheet->setCellValue("F{$line}", "分");
						}
					}
				}

				$line++;
				//图像写入操作
				if (!empty($qaBank['image_url'])) {
					// https://uploads.jiankangpao.sichuancancer.org/uploads/images/20230321/071992e0911eeb323106df815045b64b.jpg-imageView2
					$imageFile = explode('-image', explode('uploads/', $qaBank['image_url'])[1])[0];
					$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
					$drawing->setWorksheet($sheet);
					$drawing->setPath(ROOT_PATH . '..' . DS . 'uploads' . DS . $imageFile);
					// $drawing->setWidth(100); //图片宽
					$drawing->setHeight(100); //图片高 100像素
					$drawing->setOffsetX(2); //设置图片偏移量
					$drawing->setCoordinates("D{$line}"); //将图片放置于单元格

					$sheet->getRowDimension($line)->setRowHeight(78); //75磅

					$sheet->getStyle("D{$line}")->getBorders()->getLeft()->setBorderStyle(Border::BORDER_MEDIUMDASHED)->setColor(new Color('FFFF0000'));
					$line++;
				}

				$answerList = json_decode($qaBank['qa_json'], true);
				foreach ($answerList as $key => $answer) {
					// $sheet->setCellValue("B{$line}", "分值：")
					$sheet->setCellValue("C{$line}", $answer['score'])
						->setCellValue("D{$line}", $answer['text'])->getStyle("D{$line}")->getBorders()->getLeft()->setBorderStyle(Border::BORDER_MEDIUMDASHED)->setColor(new Color('FFFF0000'));
					$sheet->getStyle("B{$line}")->getAlignment()->applyFromArray($this->conf['alignment_right'])->setWrapText(false);
					$sheet->getStyle("C{$line}")->getAlignment()->applyFromArray($this->conf['alignment_left'])->setWrapText(false);
					$line++;
				}
				$sheet->getStyle("A{$line}:H{$line}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFBFBFBF'); //设置背景色
				$line++;
			}
		}

		$line++;
		$sheet->setCellValue("A{$line}", "");

		//设置打印页边距
		$sheet->getPageMargins()->setTop(0);
		$sheet->getPageMargins()->setRight(0);
		$sheet->getPageMargins()->setLeft(0);
		$sheet->getPageMargins()->setBottom(0);
		//设置纸张类型
		$sheet->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);
		//设置自动筛选
		// $sheet->setAutoFilter('A5:D5');
		//设置自动换行
		// $sheet->getStyle('B6:B5')->getAlignment()->setWrapText(true);

		$writer = new Xls($spreadsheet);

		$excelFile =  "Qa{$qa_main_id}" . '.xls';
		$filePath = ROOT_PATH . '..' . DS . 'uploads' . DS  . $excelFile;
		if (file_exists($filePath)) {
			unlink($filePath);
		}
		$writer->save(ROOT_PATH . '..' . DS . 'uploads' . DS  . $excelFile);

		// $url = 'https://uploads.jiankangpao.sichuancancer.org/uploads' . DS  . $excelFile;
		$url = 'https://jiankangpao.sichuancancer.org/uploads' . DS  . $excelFile;
		return $url;
	}

	// EXCEL导入
	// https://jiankangpao.sichuancancer.org/azfzjkp_dev/home/QaBank/importExcel/qa_main_id/96.html?excel_url=https://uploads.jiankangpao.sichuancancer.org/uploads/other/20230417/c3129ad17732e8304f8781e365687c72.xls
	// https://jiankangpao.sichuancancer.org/azfzjkp_dev/home/QaBank/importExcel/qa_main_id/96.html?excel_url=https://uploads.jiankangpao.sichuancancer.org/uploads/other/20230417/4c0b546e2483e00e5044e46dd75adb65.xlsx
	public function setExcel($qa_main_id, $import_type, $excel_url)
	{
		$qaMain = db("qa_main")->where("id", $qa_main_id)->find();
		$rt = ['code' => 0, 'msg' => ''];
		if (empty($qaMain)) {
			// 应该不存在为空的情况
			$rt = ['code' => -1, 'msg' => '参数异常'];
		} else {
			// 启动事务
			db()->transaction(function () use ($qaMain, $qa_main_id, $import_type, $excel_url, &$rt) {
				// import_type append:追加导入；replace：全新导入
				if ($import_type == 'replace') {
					// replace：全新导入，先删除原有数据
					db("qa_bank")->where("qa_main_id", $qa_main_id)->delete();
					$classify_set = [];
				} else {
					$classify_set = explode("，", $qaMain['classify_set']);
				}

				$pos = strpos($excel_url, "://");
				if ($pos !== false) {
					// 是网络连接
					$excel_url = explode('uploads/', $excel_url)[1];
					// echo "{$excel_url} " . "<br>" . PHP_EOL;
					$path = ROOT_PATH . '..' . DS . 'uploads' . DS . $excel_url;
				} else {
					$path = $excel_url;
				}

				$pathInfo = pathinfo($path);
				if (strtolower($pathInfo['extension']) == 'xls') {
					$reader = IOFactory::createReader('Xls');
				} else {
					$reader = IOFactory::createReader('Xlsx');
				}
				// $reader->setReadDataOnly(TRUE);//不能启用，启用后不能读取到excel中的图片
				$spreadsheet = $reader->load($path); //载入excel表格

				$sheet = $spreadsheet->getActiveSheet();

				$highestRow = $sheet->getHighestRow() + 2; // 总行数
				// $highestColumn = $sheet->getHighestColumn(); // 总列数

				$qa_bank_list = [];
				$qa_bank = [];
				$qa_json = [];
				$count = 0;
				$update = 0;
				for ($row = 13; $row <= $highestRow; ++$row) {
					$vA = trim($sheet->getCell("A{$row}")->getValue()); //题号q_no
					$vB = trim($sheet->getCell("B{$row}")->getValue()); //分类因子
					$vC = trim($sheet->getCell("C{$row}")->getValue()); //题型（单选/多选）；分数
					$vD = trim($sheet->getCell("D{$row}")->getValue()); //题目；图片
					$vF = trim($sheet->getCell("F{$row}")->getValue()); //解析

					// echo "<br>" . PHP_EOL . "vA：{$vA} vB：{$vB} vC：{$vC} vD：{$vD} " . "<br>" . PHP_EOL;
					if (!empty($vA) && !empty($vC) && !empty($vD)) { //&& !empty($vB) B因子可能为空
						// 找到题目
						// echo "<br>" . PHP_EOL . "找到题目" . "<br>" . PHP_EOL;
						$qa_bank = [];
						$qa_json = [];
						$qa_bank['qa_main_id'] = $qa_main_id; //问题ID
						$qa_bank['q_no'] = $vA; //题号q_no
						$qa_bank['classify'] = $vB; //分类因子
						$qa_bank['type_tag'] = $vC; //题型（单选/多选/填空）
						$qa_bank['title'] = $vD; //题目
						$qa_bank['analysis'] = $vF; //解析
						// $qa_bank['count_mode'] = [];

						// if ($vC == '多选-数量记分') {
						// 	// 取得数量记分参数
						// 	$row++;
						// 	// 选择个数大于	3	得	1	分
						// 	$mcAbove = trim($sheet->getCell("C{$row}")->getValue()); //大于
						// 	$mcScore = trim($sheet->getCell("E{$row}")->getValue()); //得分
						// 	if ($mcAbove > 0 && $mcScore > 0) {
						// 		$qa_bank['count_mode'] = ['mode' => 'above_count', 'above' => 3, 'score' => 1];
						// 	}
						// }
					} else if (empty($vA) && empty($vB) && strlen($vC) < 1 && !empty($images["D{$row}"])) {
						// 找到图片
						// echo "<br>" . PHP_EOL . "找到图片" . "<br>" . PHP_EOL;
						$qa_bank['image_url'] = $images["D{$row}"]; //图片
					} else if (empty($vA) && empty($vB) && (is_numeric($vC) || $vC == '0') && !empty($vD)) {
						// 找到答案
						// echo "<br>" . PHP_EOL . "找到答案" . "<br>" . PHP_EOL;
						$qa_json[] = ['score' => $vC, 'text' => $vD]; //答案
					} else if (empty($vA) && empty($vB) && empty($vC) && empty($vD)) {
						// echo "<br>" . PHP_EOL . "找到隔行" . "<br>" . PHP_EOL;
						// echo  "<br>" . PHP_EOL . "qa_bank " . "<br>" . PHP_EOL;
						// var_dump($qa_bank);
						// echo "<br>" . PHP_EOL . "qa_json " . "<br>" . PHP_EOL;
						// var_dump($qa_json);
						// 找到隔行
						if (!empty($qa_bank)) {
							// 成功找到一个问题导入到数据库
							$classify_set[] = $qa_bank['classify'];

							$score = 0;
							foreach ($qa_json as $k => $v) {
								if (!empty($v['text'])) {
									// $v['score'] = $v['score'] > 0 ? $v['score'] : 0;
									$v['score'] = is_numeric($v['score']) ? $v['score'] : 0;

									if ($qa_bank['type_tag'] == '多选') {
										// 多选题 分数和
										$score += $v['score'];
									} else if ($score < $v['score']) {
										// 单选题选最高分
										$score = $v['score'];
									}
									$qa_json[$k] = array('score' => $v['score'], 'text' => $v['text'], 'is_selected' => 'no', 'input_value' => '');
								} else {
									unset($qa_json[$k]);
								}
							}

							$qa_bank['qa_json'] = json_encode($qa_json);
							$qa_bank['status'] = 1;
							// // 本题最高分
							// if (isset($qa_bank['count_mode']['mode']) && $qa_bank['count_mode']['mode'] == 'above_count') {
							// 	$qa_bank['score'] = $qa_bank['count_mode']['score'];
							// } else {
							// 	$qa_bank['score'] = $score;
							// }
							// $qa_bank['count_mode'] = json_encode($qa_bank['count_mode']);

							$qa_bank_list[] = $qa_bank;
							// array_unshift($qa_bank_list, $qa_bank); //在数组的开头插入一个元素，解决导入后倒序的问题
							$qa_bank = [];
							$qa_json = [];
						}
					}
				}

				// 更新题目
				foreach ($qa_bank_list as $key => $qa_bank) {
					if ($import_type == 'update') {
						$bank = db("qa_bank")->where(['qa_main_id' => $qa_bank['qa_main_id'], 'title' => $qa_bank['title']])->find();
						if (!empty($bank)) {
							db("qa_bank")->where(['id' => $bank['id']])->update($qa_bank);
							$update++;
						} else {
							db("qa_bank")->insert($qa_bank);
							$count++;
						}
					} else {
						db("qa_bank")->insert($qa_bank);
						$count++;
					}
				}

				$rt = ['code' => 0, 'msg' => "添加：{$count}个题；更新：{$update}个题；"];
			});
			return $rt;
		}
	}

	// EXCEL导入市州对应题目
	public function setRegionExcel($path)
	{
		// 启动事务
		db()->transaction(function () use ($path, &$rt) {
			$pathInfo = pathinfo($path);
			if (strtolower($pathInfo['extension']) == 'xls') {
				$reader = IOFactory::createReader('Xls');
			} else {
				$reader = IOFactory::createReader('Xlsx');
			}
			// $reader->setReadDataOnly(TRUE);//不能启用，启用后不能读取到excel中的图片
			$spreadsheet = $reader->load($path); //载入excel表格

			$sheet = $spreadsheet->getActiveSheet();

			$highestRow = $sheet->getHighestRow() + 1; // 总行数

			for ($row = 2; $row <= $highestRow; $row++) {
				$region = trim($sheet->getCell("A{$row}")->getValue()); //市州
				$qaNoList = [];
				$qaNoList[] = trim($sheet->getCell("B{$row}")->getValue()); //题号1
				$qaNoList[] = trim($sheet->getCell("C{$row}")->getValue()); //题号2
				$qaNoList[] = trim($sheet->getCell("D{$row}")->getValue()); //题号3
				if (!empty($region)) {
					foreach ($qaNoList as $key => $qaNo) {
						if (!empty($qaNo)) {
							echo "市州：{$region} 题号：{$qaNo}<br>" . PHP_EOL;
							// 更新题目
							$qaBank = db("qa_bank")->where(['classify' => '闯关题', 'q_no' => $qaNo])->find();
							// var_dump($qaBank);
							if (!empty($qaBank)) {
								db("qa_bank")->where('id', $qaBank['id'])->update(['region' => $region]);

								// echo PHP_EOL . db("qa_bank")->getLastSql() . "<br>" . PHP_EOL;
							}
						}
					}
				}
			}
		});
		return $rt;
	}
}
